#!/anaconda3/envs/FEALPy/bin python3.8
# -*- coding: utf-8 -*-
# File: data_utils.py
# Author: Bryan SHEN
# E-mail: m18801919240_3@163.com
# Site: Shanghai, China
# Time: 2024/11/1 00:31
# File-Desp:

import pandas as pd
from sqlalchemy.engine import create_engine
from datetime import timedelta
import datetime

engine = create_engine('presto://u_algorithm@trino.aihuishou.com:9090/hive/default')
current_date = datetime.datetime.now().date()  # 获取当前日期
yest_date = current_date - timedelta(days=1)  # 获取昨天的日期
current_date_str = str(current_date)


# 2025-02-21 所有跟优惠券相关的逻辑，全部替换成实时计算的
conpon_sql = """
    select 
        * 
    from 
        ods.ods_c2babtrader_competitive_price_coupon_detail
    where partition_date = '{}'
""".format(current_date_str)

couponed_df = pd.read_sql(conpon_sql, engine)
coupon_ahs = couponed_df[couponed_df['config_id'] == 1]
coupon_zz = couponed_df[couponed_df['config_id'] == 3]


def calculate_ahs_couponed_price(price):
    # 匹配爱回收的券数据
    # coupon_data = couponed_df[couponed_df['config_id'] == 1]

    coupon = 0
    rated_price = price
    for _, row in coupon_ahs.iterrows():
        if row['coupon_type'] == 0:  # 阶梯型券
            if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                coupon = row['coupon_price']
        elif row['coupon_type'] == 1:  # 固定比例券
            if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                rated_price = price * (1 + row['coupon_price'])  # 小数比例计算
    return coupon + rated_price


def calculate_zz_couponed_price(price):
    # 匹配转转的券数据
    # coupon_data = couponed_df[couponed_df['config_id'] == 3]

    coupon = 0
    rated_price = price
    for _, row in coupon_zz.iterrows():
        if row['coupon_type'] == 1:  # 固定比例券
            if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                rated_price = price * (1 + row['coupon_price'])  # 小数比例计算
        elif row['coupon_type'] == 0:  # 阶梯型券
            if row['price_range_operator'] == 'between' and row['price_range_min'] <= price < row['price_range_max']:
                coupon = row['coupon_price']

    return coupon + rated_price


# 2025-02-21 从之前写死的券逻辑更新为随数据库实时更新的版本
def calculate_strategy_value(price):
    """ 通过mysql中的券数据直接计算 获得 <转转追价-策略-中间值> """

    for _, row in coupon_ahs.iterrows():
        # 2025-04-08 AHS除了阶梯型券，也新增设比例券
        if row['coupon_type'] == 0:  # 阶梯型券
            if row['price_range_min'] <= price <= row['price_range_max'] + row['coupon_price']:
                return price - row['coupon_price']
        elif row['coupon_type'] == 1:  # 固定比例券
            if row['price_range_min'] <= price <= row['price_range_max'] * (1 + row['coupon_price']):
                return price / (1 + row['coupon_price'])
    return price


# # 2025-02-21 从之前写死的券逻辑更新为随数据库实时更新的版本
# def calculate_strategy_value(price):
#     """ 通过mysql中的券数据直接计算 """
#
#     for _, row in coupon_ahs.iterrows():
#         if row['price_range_min'] <= price <= row['price_range_max'] + row['coupon_price']:
#             return price - row['coupon_price']
#
#     return price


# 2025-02-21 从之前写死的券逻辑更新为随数据库实时更新的版本
def mapping_coupon_price_boundary(row):
    """ 替换写死的ahs优惠券边界逻辑 """

    price = row['zz_couponed_price_18_added']

    min_boundary = coupon_ahs['price_range_min'].min()
    max_boundary = coupon_ahs['price_range_min'].max()

    if price < min_boundary:
        return 0
    for _, row in coupon_ahs.iterrows():
        # 2025-04-08 AHS除了阶梯型券，也新增设比例券
        if row['coupon_type'] == 0:  # 阶梯型券
            if row['price_range_min'] <= price <= row['price_range_max'] + row['coupon_price']:
                return int(row['price_range_min'])
        elif row['coupon_type'] == 1:  # 固定比例券
            if row['price_range_min'] <= price <= row['price_range_max'] * (1 + row['coupon_price']):
                return int(row['price_range_min'])

    return max_boundary


# # 2025-02-21 从之前写死的券逻辑更新为随数据库实时更新的版本
# def mapping_coupon_price_boundary(row):
#     """ 替换写死的ahs优惠券边界逻辑 """
#
#     price = row['zz_couponed_price_18_added']
#
#     min_boundary = coupon_ahs['price_range_min'].min()
#     max_boundary = coupon_ahs['price_range_min'].max()
#
#     if price < min_boundary:
#         return 0
#     for _, row in coupon_ahs.iterrows():
#         if row['price_range_min'] <= price <= row['price_range_max'] + row['coupon_price']:
#             return int(row['price_range_min'])
#
#     return max_boundary


# 十位取整逻辑（取最后两位数）
def rounding_to_tens(price):

    # if price == 0:
    #     return 0
    if 0 <= price < 1:
        return 1
    elif 1 <= price < 40:
        return 40
    elif 40 <= price < 50:
        return 50
    elif 50 <= price < 60:
        return 60
    elif price >= 60:
        return 100


def calculate_strategy_quote_price(row):

    try:
        if row['券后价差-ahs-转转'] < 0:

            # 1，将 '转转追价-策略-中间值' 的十位结果转换之后的价格 (eg. 2160)
            int_part = (row['转转追价-策略-中间值'] // 100) * 100  # 获取百位及以上值，这里是：2100
            mod_part = row['转转追价-策略-中间值'] % 100  # 获取十位及以下值，这里是：60
            tens = rounding_to_tens(mod_part)  # 获取十位取整结果，这里是：100
            lookup_value1 = int_part + tens

            # 2，将 '转转新人券后价' 映射到AHS优惠券的 “门槛价格”
            lookup_value2 = mapping_coupon_price_boundary(row)  # 获取优惠券边界值，这里是：2000

            # 1和2为什么取两者中的最大值
            return max(lookup_value1, lookup_value2)  # max(2100+100, 2000) = 2200
        else:
            # 3，AHS券前价
            # return row['ahs_ori_price']
            return row['finalprice']
    except:

        return 0


# 定义等级顺序
level_order = [
    "A+", "A-", "A1", "A", "A2",
    "B+1", "B+2", "B1", "B", "B2",
    "C+1", "C+", "C+2", "C1", "C", "C2",
    "D+1", "D+", "D+2", "D1", "D", "D2"
]

level_hierarchy = [
    ("A+", "A-"),
    ("A+", "A1"),
    ("A+", "A"),
    ("A-", "A2"),
    ("A1", "A2"),
    ("A", "A2"),
    ("A-", "B+1"),
    ("B+1", "B+2"),
    ("A1", "B1"),
    ("B1", "B"),
    ("B1", "B2"),
    ("B+1", "B2"),
    ("A", "C+1"),
    ("A", "C+"),
    ("B1", "C+2"),
    ("C+1", "C1"),
    ("C+", "C1"),
    ("B+1", "C1"),
    ("B1", "C1"),
    ("B2", "C1"),  # 2025-03-26
    ("C+2", "C"),
    ("C+1", "C"),
    ("C+", "C"),
    ("B+1", "C"),
    ("C", "C2")
]


def check_price_inversion(df, sku, parent, child, price_column):

    parent_price = df.loc[(df['product_sku_name'] == sku) & (df['product_level_name'] == parent), price_column]
    child_price = df.loc[(df['product_sku_name'] == sku) & (df['product_level_name'] == child), price_column]

    # 父价格和子价格都不为空的情况下，子价格 > 父价格 时，出现倒挂 +1
    if not parent_price.empty and not child_price.empty:
        # return parent_price.values[0] <= child_price.values[0]
        return parent_price.values[0] < child_price.values[0]  # 2025-02-18 去掉=
    return False


def calculate_inversion_statistics(df, price_column):
    """ 计算倒挂率"""

    results = []

    # 计算倒挂率
    relationships_df = pd.DataFrame(level_hierarchy, columns=['parent_level', 'child_level'])

    # Iterate through each unique SKU
    for sku in df['product_sku_name'].unique():
        total_comparisons = 0
        inversion_count = 0
        # Iterate through each parent-child relationship to check inversions
        for _, row in relationships_df.iterrows():
            parent_level = row['parent_level']
            child_level = row['child_level']
            if (df['product_sku_name'] == sku).any() and \
                    (df['product_level_name'] == parent_level).any() and \
                    (df['product_level_name'] == child_level).any():
                total_comparisons += 1
                if check_price_inversion(df, sku, parent_level, child_level, price_column):
                    inversion_count += 1
        # Append result for this SKU
        results.append({
            'product_sku_name': sku,
            'total_comparisons': total_comparisons,
            'inversion_count': inversion_count
        })

    return pd.DataFrame(results)


def define_competiitive_starts_ends(human_competitive_rate):
    """ 系统苹果型号目前所用数据 """

    starts, ends = 0, 0
    if human_competitive_rate <= 0.0:
        starts, ends = 0, 0
    elif 0.0 < human_competitive_rate <= 0.1:
        # starts = 0
        # ends = 15
        starts = 0
        ends = 150
    elif 0.1 < human_competitive_rate < 0.8:
        # starts = int((human_competitive_rate - 0.1) * 100)
        # ends = int((human_competitive_rate + 0.2) * 100)
        starts = int((human_competitive_rate - 0.1) * 1000)
        ends = int((human_competitive_rate + 0.2) * 1000)
    elif human_competitive_rate >= 0.8:
        starts = 790
        ends = 990

    return starts, ends


# BI: 计算 "修差异过大后券前价格" 字段价格
def calculate_big_gap_value(row):
    """ 基于不同等级，考虑不同row['同sku的A+券前价']区间以及不同row['价差比']的调价逻辑
        涉及大量参数：都是业务根据‘属性项/值’的实际情况给出的参考值
    """

    if row['product_level_name'] == "A1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A-":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A2":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.74, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.18:
            return round(row['同sku的A+券前价'] * 0.825, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.16:
            return round(row['同sku的A+券前价'] * 0.845, 0)
    elif row['product_level_name'] == "B1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.35:
            return round(row['同sku的A+券前价'] * 0.655, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.705, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.25:
            return round(row['同sku的A+券前价'] * 0.755, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.805, 0)
    elif row['product_level_name'] == "A":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "B2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
        elif row['同sku的A+券前价'] >= 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
    elif row['product_level_name'] == "C+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.33:
            return round(row['同sku的A+券前价'] * 0.675, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
    elif row['product_level_name'] == "C1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
    elif row['product_level_name'] == "D+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.48:
            return round(row['同sku的A+券前价'] * 0.525, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.45:
            return round(row['同sku的A+券前价'] * 0.555, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.4:
            return round(row['同sku的A+券前价'] * 0.605, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
    # 下面3个为新增等级
    elif row['product_level_name'] == "B+1":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.71, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.745, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.815, 0)
    elif row['product_level_name'] == "B+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.7, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        # elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.21:
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
    elif row['product_level_name'] == "D+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.46:
            return round(row['同sku的A+券前价'] * 0.545, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)

    return round(row['adjusted_price'], 0)


# human: 计算 "修差异过大后券前价格" 字段价格
def calculate_big_gap_value_human(row):
    """ 基于不同等级，考虑不同row['同sku的A+券前价']区间以及不同row['价差比']的调价逻辑
        涉及大量参数：都是业务根据‘属性项/值’的实际情况给出的参考值
    """

    if row['product_level_name'] == "A1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A-":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A2":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.74, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.18:
            return round(row['同sku的A+券前价'] * 0.825, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.16:
            return round(row['同sku的A+券前价'] * 0.845, 0)
    elif row['product_level_name'] == "B1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.35:
            return round(row['同sku的A+券前价'] * 0.655, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.705, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.25:
            return round(row['同sku的A+券前价'] * 0.755, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.805, 0)
    elif row['product_level_name'] == "A":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "B2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
        elif row['同sku的A+券前价'] >= 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
    elif row['product_level_name'] == "C+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.33:
            return round(row['同sku的A+券前价'] * 0.675, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
    elif row['product_level_name'] == "C1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
    elif row['product_level_name'] == "D+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.48:
            return round(row['同sku的A+券前价'] * 0.525, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.45:
            return round(row['同sku的A+券前价'] * 0.555, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.4:
            return round(row['同sku的A+券前价'] * 0.605, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
    # 下面3个为新增等级
    elif row['product_level_name'] == "B+1":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.71, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.745, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.815, 0)
    elif row['product_level_name'] == "B+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.7, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        # elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.21:
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
    elif row['product_level_name'] == "D+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.46:
            return round(row['同sku的A+券前价'] * 0.545, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)

    return round(row['human_price'], 0)


# human: 计算 "修差异过大后券前价格" 字段价格
def calculate_big_gap_value_human_g2(row):
    """ 基于不同等级，考虑不同row['同sku的A+券前价']区间以及不同row['价差比']的调价逻辑
        涉及大量参数：都是业务根据‘属性项/值’的实际情况给出的参考值
    """

    if row['product_level_name'] == "A1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A-":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "A2":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.74, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.18:
            return round(row['同sku的A+券前价'] * 0.825, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.16:
            return round(row['同sku的A+券前价'] * 0.845, 0)
    elif row['product_level_name'] == "B1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.35:
            return round(row['同sku的A+券前价'] * 0.655, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.705, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.25:
            return round(row['同sku的A+券前价'] * 0.755, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.805, 0)
    elif row['product_level_name'] == "A":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.2:
            return round(row['同sku的A+券前价'] * 0.81, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.17:
            return round(row['同sku的A+券前价'] * 0.835, 0)
        elif 3000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.15:
            return round(row['同sku的A+券前价'] * 0.855, 0)
        elif 5000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.12:
            return round(row['同sku的A+券前价'] * 0.885, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.1:
            return round(row['同sku的A+券前价'] * 0.905, 0)
    elif row['product_level_name'] == "B2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif 2000 <= row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
        elif row['同sku的A+券前价'] >= 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
    elif row['product_level_name'] == "C+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.33:
            return round(row['同sku的A+券前价'] * 0.675, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.31:
            return round(row['同sku的A+券前价'] * 0.695, 0)
    elif row['product_level_name'] == "C1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.39:
            return round(row['同sku的A+券前价'] * 0.615, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.34:
            return round(row['同sku的A+券前价'] * 0.665, 0)
    elif row['product_level_name'] == "D+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.48:
            return round(row['同sku的A+券前价'] * 0.525, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.45:
            return round(row['同sku的A+券前价'] * 0.555, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.4:
            return round(row['同sku的A+券前价'] * 0.605, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
    # 下面3个为新增等级
    elif row['product_level_name'] == "B+1":
        if row['同sku的A+券前价'] < 3000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.71, 0)
        elif 3000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.26:
            return round(row['同sku的A+券前价'] * 0.745, 0)
        elif 4000 <= row['同sku的A+券前价'] < 5000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
        elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.29:
            return round(row['同sku的A+券前价'] * 0.815, 0)
    elif row['product_level_name'] == "B+2":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.3:
            return round(row['同sku的A+券前价'] * 0.7, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.27:
            return round(row['同sku的A+券前价'] * 0.735, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.23:
            return round(row['同sku的A+券前价'] * 0.775, 0)
        # elif row['同sku的A+券前价'] >= 5000 and row['价差比'] > 0.21:
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.21:
            return round(row['同sku的A+券前价'] * 0.795, 0)
    elif row['product_level_name'] == "D+1":
        if row['同sku的A+券前价'] < 2000 and row['价差比'] > 0.46:
            return round(row['同sku的A+券前价'] * 0.545, 0)
        elif 2000 <= row['同sku的A+券前价'] < 4000 and row['价差比'] > 0.43:
            return round(row['同sku的A+券前价'] * 0.575, 0)
        elif 4000 <= row['同sku的A+券前价'] < 6000 and row['价差比'] > 0.38:
            return round(row['同sku的A+券前价'] * 0.625, 0)
        elif row['同sku的A+券前价'] >= 6000 and row['价差比'] > 0.36:
            return round(row['同sku的A+券前价'] * 0.645, 0)

    return round(row['human_price'], 0)



# # 生成爱回收的 "券后价"
# def calculate_couponed_price(price):
#     # ahs券 1115-1201
#     if price < 200:
#         return price
#     elif 200 <= price < 400:
#         return price + 40
#     elif 400 <= price < 800:
#         return price + 80
#     elif 800 <= price < 1200:
#         return price + 160
#     elif 1200 <= price < 2000:
#         return price + 240
#     elif 2000 <= price < 3000:
#         return price + 350
#     elif 3000 <= price < 4800:
#         return price + 450
#     else:
#         return price + 550


# def calculate_couponed_price(price):
#     # ahs券 1023-1111
#     if price < 200:
#         return price
#     elif 200 <= price < 400:
#         return price + 40
#     elif 400 <= price < 800:
#         return price + 80
#     elif 800 <= price < 1200:
#         return price + 150
#     elif 1200 <= price < 2000:
#         return price + 220
#     elif 2000 <= price < 3000:
#         return price + 320
#     elif 3000 <= price < 4800:
#         return price + 450
#     else:
#         return price + 500


# # 生成新字段
# def calculate_zz_couponed_18_added_price(row):
#
#     # price = int(row['zz_ori_price'])
#     price = int(row['zz_pre_coupon_price'])
#
#     if price < 400:
#         return int(row['zz_couponed_price_18'])
#     elif 400 <= price < 900:
#         return int(row['zz_couponed_price_18']) + 10
#     elif 900 <= price < 1800:
#         return int(row['zz_couponed_price_18']) + 30
#     elif 1800 <= price < 3000:
#         return int(row['zz_couponed_price_18']) + 60
#     elif 3000 <= price < 6000:
#         return int(row['zz_couponed_price_18']) + 110
#     elif 6000 <= price < 9000:
#         return int(row['zz_couponed_price_18']) + 130
#     elif price >= 9000:
#         return int(row['zz_couponed_price_18']) + 220


# # 生成新字段 "转转追价-策略-中间值"
# def calculate_strategy_value(price):
#
#     if price <= 200:
#         return price
#     elif 200 < price <= 440:
#         return price - 40
#     elif 440 < price <= 880:
#         return price - 80
#     elif 870 < price <= 1350:
#         return price - 150
#     elif 1350 < price <= 2220:
#         return price - 220
#     elif 2220 < price <= 3320:
#         return price - 320
#     elif 3320 < price <= 5250:
#         return price - 450
#     else:
#         return price - 500


# def calculate_strategy_value(price):
#
#     if price < 200:
#         return price
#     elif 200 <= price < 450:
#         return price - 50
#     elif 450 <= price < 900:
#         return price - 100
#     elif 900 <= price < 1400:
#         return price - 200
#     elif 1400 <= price < 2300:
#         return price - 300
#     elif 2300 <= price < 3400:
#         return price - 400
#     elif 3400 <= price < 5300:
#         return price - 500
#     else:
#         return price - 580


# # 十位取整逻辑（取最后两位数）
# def rounding_to_tens(price):
#
#     # if price == 0:
#     #     return 0
#     if 0 <= price < 1:
#         return 1
#     elif 1 <= price < 40:
#         return 40
#     elif 40 <= price < 50:
#         return 50
#     elif 50 <= price < 60:
#         return 60
#     elif price >= 60:
#         return 100


# 映射出优惠券的 “价格区间” 和 “边界”
# def mapping_coupon_price_boundary(row):
#
#     price = row['zz_couponed_price_18_added']
#
#     if 0 < price <= 200:
#         return 0
#     elif 200 < price <= 400:
#         return 200
#     elif 440 < price <= 880:
#         return 400
#     elif 880 < price <= 1350:
#         return 800
#     elif 1350 < price <= 2220:
#         return 1200
#     elif 2220 < price <= 3320:
#         return 2000
#     elif 3320 < price <= 5250:
#         return 3000
#     elif price > 5250:
#         return 4800


# def mapping_coupon_price_boundary(row):
#     """ 2025-02-21 判断转转券后最终价所处的边界 """
#
#     # 转转券后最终价
#     price = row['zz_couponed_price_18_added']
#
#     if 0 < price < 200:
#         return 0
#     elif 200 <= price < 450:
#         return 200
#     elif 450 <= price < 900:
#         return 400
#     elif 900 <= price < 1400:
#         return 800
#     elif 1400 <= price < 2300:
#         return 1200
#     elif 2300 <= price < 3400:
#         return 2000
#     elif 3400 <= price < 5300:
#         return 3000
#     elif price >= 5300:
#         return 4800
#
#
# # 生成新字段 "转转追价-策略报价"
# def calculate_strategy_quote_price(row):
#
#     try:
#         if row['券后价差-ahs-转转'] < 0:
#
#             # 1，将 '转转追价-策略-中间值' 的十位结果转换之后的价格 (eg. 2160)
#             int_part = (row['转转追价-策略-中间值'] // 100) * 100  # 获取百位及以上值，这里是：2100
#             mod_part = row['转转追价-策略-中间值'] % 100  # 获取十位及以下值，这里是：60
#             tens = rounding_to_tens(mod_part)  # 获取十位取整结果，这里是：100
#             lookup_value1 = int_part + tens
#
#             # 2，将 '转转新人券后价' 映射到AHS优惠券的 “门槛价格”
#             lookup_value2 = mapping_coupon_price_boundary(row)  # 获取优惠券边界值，这里是：2000
#
#             # 1和2为什么取两者中的最大值
#             return max(lookup_value1, lookup_value2)  # max(2100+100, 2000) = 2200
#         else:
#             # 3，AHS券前价
#             # return row['ahs_ori_price']
#             return row['finalprice']
#     except:
#
#         return 0


# 生成新字段 "转转追价-策略报价-券后"
def calculate_strategy_couponed_price(price):

    if price < 200:
        return price
    elif 200 <= price < 400:
        return price + 40
    elif 400 <= price < 800:
        return price + 80
    elif 800 <= price < 1200:
        return price + 150
    elif 1200 <= price < 2000:
        return price + 220
    elif 2000 <= price < 3000:
        return price + 320
    elif 3000 <= price < 4800:
        return price + 450
    else:
        return price + 500

